#!/usr/bin/env bash

dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -uroot -proot -hhadoop2<<EOF
use sqlserver;
select B.substrb,B.station_id,A.count,A.sum,B.count-A.count,B.sum-A.sum,B.count,B.sum
from
(select substr(transaction_date,1,13)as substrb,station_id,count(transaction_id)as count,sum(sale_total)as sum
from ( SELECT transaction_date,transaction_id,station_id,sale_total from transactions where substr(transaction_date,1,10)='$dt' )as t
group by substr(transaction_date,1,13),station_id)as B
join
(select substr(Z.transaction_date,1,13)as substra,Z.station_id,count(Z.transaction_id)as count,sum(Z.sale_total)as sum
from
( SELECT transaction_date,transaction_id,station_id,sale_total,cashier_id from transactions where substr(transaction_date,1,10)='$dt' )as Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
group by substr(Z.transaction_date,1,13),Z.station_id) as A
on A.substra=B.substrb AND A.station_id=B.station_id;
EOF